Executive Summary
iWorQ, a leading software provider for local government agencies, tasked my team with enhancing an existing dashboard that tracks usage and account activity for their various applications. The goal was to build on the prior work and introduce new functionality to help iWorQ make data-driven decisions about user engagement, cancellations, and active usage trends.
Project Scope
The key objectives of the project were:
- Clickable application types: Provide the ability to view usage data by application (e.g., Permit, Code Enforcement), with details on accounts using each application frequently or infrequently.
- Spikes in usage: Identify changes in usage behavior, such as sudden spikes or drop-offs.
- Most active accounts: Highlight accounts that are highly engaged across multiple applications.
- User Information: Enable users to access contact details and usage information for each account and application.
- Automated Updates: Implement real-time or periodic (weekly/monthly) updates to ensure the data reflects the latest user activity and cancellations.
- Cancellation and inactivity tracking: Accurately account for cancellations, ensuring inactive applications are not mistaken for under-utilization.
Technical Process
We used Python, specifically the awswrangler and boto3 libraries, to pull data from an AWS S3 bucket. The S3 bucket contained partitioned Parquet files with historical usage data, spanning over two years. The code I developed allowed us to extract this data efficiently by filtering the relevant data using year and week partitions, ensuring only necessary data was retrieved and processed.
The filtering logic was crucial, as it ensured that data from the appropriate time frame (two years back) was pulled without retrieving unnecessary records. The following snippet illustrates the filtering process for pulling data from the S3 bucket:
Dashboard Features
The final dashboard provides:
- Overview of Usage Data: For each application, users can view which accounts are using it the most or the least.
- Active Accounts and Users: A list of the most active accounts, including detailed information about which applications are being used and the key contacts associated with each.
- Trend Identification: The dashboard automatically flags usage trends, showing increases or decreases in account activity.
- Automated Data Refresh: The dashboard is updated on a scheduled basis (weekly or monthly), pulling the latest data from the S3 bucket to reflect real-time usage.